PRAGMA AUTONOMOUS

2023-08-30 00:37| 来源: 网络整理| 查看: 265

这段时间遇到一个问题，程序里明明插入了一条记录，但在后边的一段Procedure中却查不到刚刚插入的记录,最后发现这个Procedure的定义中加入了PRAGMA AUTONOMOUS_TRANSACTION。

PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”（翻译的还算好理解），对于定义成自治事务的Procedure，实际上相当于一段独立运行的程序段，这段程序不依赖于主程序，也不干涉主程序

自治事务的特点

第一,这段程序不依赖于原有Main程序，比如Main程序中有未提交的数据，那么在自治事务中是查找不到的。

第二,在自治事务中，commit或者rollback只会提交或回滚当前自治事务中的DML，不会影响到Main程序中的DML。

Autonomous Transaction Demo 1

Without Pragma Autonomous Transaction

CREATE TABLE t ( test_value VARCHAR2(25)); CREATE OR REPLACE PROCEDURE child_block IS BEGIN INSERT INTO t (test_value) VALUES ('Child block insert'); COMMIT; END child_block; / CREATE OR REPLACE PROCEDURE parent_block IS BEGIN INSERT INTO t (test_value) VALUES ('Parent block insert'); child_block; ROLLBACK; END parent_block; / -- run the parent procedure exec parent_block -- check the results SELECT * FROM t;

Output: Parent block insert Child block insert With Pragma Autonomous Transaction CREATE OR REPLACE PROCEDURE child_block IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO t (test_value) VALUES ('Child block insert'); COMMIT; END child_block; / CREATE OR REPLACE PROCEDURE parent_block IS BEGIN INSERT INTO t (test_value) VALUES ('Parent block insert'); child_block; ROLLBACK; END parent_block; / -- empty the test table TRUNCATE TABLE t; -- run the parent procedure exec parent_block; -- check the results SELECT * FROM t; Output: Child block insert

Autonomous Transaction Demo 2 Without Pragma Autonomous Transaction

DROP TABLE t; CREATE TABLE t (testcol NUMBER); CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS i INTEGER; BEGIN SELECT COUNT(*) INTO i FROM t; RETURN i; END howmanyrows; / CREATE OR REPLACE PROCEDURE testproc IS a INTEGER; b INTEGER; c INTEGER; BEGIN SELECT COUNT(*) INTO a FROM t; INSERT INTO t VALUES (1); COMMIT; INSERT INTO t VALUES (2); INSERT INTO t VALUES (3); b := howmanyrows; INSERT INTO t VALUES (4); INSERT INTO t VALUES (5); INSERT INTO t VALUES (6); COMMIT; SELECT COUNT(*) INTO c FROM t; dbms_output.put_line(a); dbms_output.put_line(b); dbms_output.put_line(c); END testproc; / set serveroutput on exec testproc Output: 0 3 6 Total execution time 2.782 sec. With Pragma Autonomous Transaction

CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS i INTEGER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN SELECT COUNT(*) INTO i FROM t; RETURN i; END howmanyrows; / -- empty the test table TRUNCATE TABLE t; exec testproc; Output: 0 1 6 转载请注明出处: http://blog.csdn.net/pan_tian/article/details/7675800

【本文地址】

公司简介

联系我们